#region (c) 2014 THN Solutions LLC. - All rights reserved
/*
THN DATA ABSTRACTION LAYER version 1

Copyright (c) 2014, THN Solutions LLC. ( www.ThnSolutions.com )
Author: Nguyễn, M. Hải                 ( www.Minh-Hai.com     )
All rights reserved.

This library is dual-licensed.
  + For commercial software, please obtain a commercial license from THN Solutions LLC.

  + For free software, this library is licensed under GPL version 3. A summary of GPLv3 is
    listed below. You should also find a copy of GPLv3 as file License_GPLv3.txt included
	with the source-code files.

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License version 3 as published by
    the Free Software Foundation  of the License.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
#endregion

#region Using directives
using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using Thn.Data.Query;
using Thn.Data.Mapping;
using Thn.Data.Exceptions;
using Thn.Data.Database.Sql.Dialect;
#endregion

namespace Thn.Data.Database.Sql
{
    /// <summary>
    /// Adapter for Microsoft SQL database
    /// </summary>
    public class MsSqlDatabase : AbstractSqlDatabase
    {
        #region Fields
        MsSqlDialect mDialect = new MsSqlDialect();
        #endregion

        #region On Insert Object
        /// <summary>
        /// Raised after insert command has been built, this method will execute the command.
        /// <para>Override this method for acquiring auto-generated IDs, etc.</para>
        /// </summary>
        /// <param name="value">the data object to be inserted</param>
        /// <param name="command">the prepared database command</param>
        /// <param name="entity">mapping of object to insert</param>
        /// <param name="attributes">mapping of attributes</param>
        /// <param name="hasAuto">Whether identifier of object is auto-generated field</param>
        protected override void OnInsertObject(object value, IDbCommand command, ObjectMap entity, AttributeMap[] attributes, bool hasAuto)
        {
            if (hasAuto)
            {//override to retrieve ID of the object inserted and put it back to the object

                //find the first identifier
                AttributeMap id = null;
                for (int i = 0; i < attributes.Length; i++)
                {
                    if (attributes[i].IsIdentifier)
                    {
                        id = attributes[i];
                        break;
                    }
                }

                //throw exception if could not find any identifier
                if (id == null) NoIdentifierException.Publish(entity.ObjectEntity);

                //build sql string & append to command
                command.CommandText += "SELECT " + id.DataAttribute + " FROM " + entity.DataEntity + " WHERE (" + id.DataAttribute + " = SCOPE_IDENTITY())";

                //execute query
                object idValue = ExecuteScalar(command, false);

                //put the new id back to the object
                if (idValue != null)
                {
                    Mapper.SetObjectAttributeValue(value, id, idValue);
                }
            }
            else base.OnInsertObject(value, command, entity, attributes, hasAuto);
        }
        #endregion

        #region I/O
        #region Refresh
        /// <summary>
        /// Refresh/load data, if neccessary
        /// </summary>
        public override void Refresh() { }
        #endregion

        #region Flush
        /// <summary>
        /// Persist changes to database, if still buffered
        /// </summary>
        public override void Flush() { }
        #endregion
        #endregion

        #region Find Identifier
        AttributeMap FindIdentifier(AttributeMap[] attributes)
        {
            AttributeMap result = null;

            for (int i = 0; i < attributes.Length; i++)
            {
                if (attributes[i].IsIdentifier)
                {
                    result = attributes[i];
                    break;
                }
                #region Defensive tracing
                else
                {
                    //not matched, keep searching
                }
                #endregion
            }

            //throw exception if can't find any identifier
            if (result == null) throw new Exception("Could not find any identifier from the provided attribute maps");
            #region Defensive tracing
            else
            {
                //found, move on
            }
            #endregion

            return result;
        }
        #endregion

        #region Build Select One Entity Clause
        /// <summary>
        /// Appends a Select record sql command to buffer
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="attributes">fields to be selected</param>
        /// <param name="criteria">condition model</param>
        /// <param name="buffer">text buffer</param>
        /// <param name="valueStack">list to append values to while parsing condition</param>
        /// <param name="query">Optional query object</param>
        protected override void BuildSelectOneEntityClause(ObjectMap entity, AttributeMap[] attributes, Condition criteria, StringBuilder buffer, Thn.Collections.ArrayList valueStack, SelectQuery query)
        {
            if (query.ItemsToSkip >= 0 && query.ItemsToTake >= 0)
            {
                #region Paging is required -> special query

                #region TECHNIQUE EXPLANATION
                /*
                 * This technique is based on using ROW_NUMBER and CTE (Common Table Expression). Only works with SQL Server 2005 or later.
                 * To read more about this technique, read: 
                 * http://sqlserverplanet.com/sql-server-denali/sql-server-denali-pagination-using-order-by/
                 * 
                 * Example sql query:
                 
                    USE [Test];
                    With SQLPaging
                    AS
                    (
	                    SELECT TOP (10)
		                    ResultNum = ROW_NUMBER() OVER (ORDER BY ID), ID, [Name], [DOB]
	                    FROM [User]
                    )
                    SELECT *
                    FROM SQLPaging
                    WHERE ResultNum > (100)
                 
                 */
                #endregion

                AttributeMap map = FindIdentifier(attributes);

                #region CTE

                //With SQLPaging
                buffer.AppendLine("With SQLPaging");

                //AS
                buffer.AppendLine("AS");

                //(
                buffer.AppendLine("(");

                //    SELECT TOP [take]
                buffer.AppendLine(string.Format("\tSELECT TOP {0}", query.ItemsToTake + query.ItemsToSkip));

                //ResultNum = ROW_NUMBER() OVER (ORDER BY [identifier]),
                buffer.AppendFormat("\tResultNum = ROW_NUMBER() OVER (ORDER BY {0}), ", map.DataAttribute);

                //        //"[Column], [Column], ..."
                BuildColumnNames(attributes, buffer, ""); buffer.AppendLine();

                //FROM [table]
                buffer.AppendLine("\tFROM [" + entity.DataEntity + "]");

                //add where clause if available
                //" WHERE [column] = @conxxx, [column] = @conxxx ..."
                if (criteria != null)
                {
                    buffer.AppendLine("WHERE " + ParseCondition(criteria, valueStack));
                }
                #region Defensive tracing
                else
                {
                    //not required
                }
                #endregion

                //" ORDER BY "
                if ((query != null) && (query.OrderByClause != null) && (query.OrderByClause.Count > 0))
                {
                    buffer.Append("\tORDER BY");

                    for (int i = 0; i < query.OrderByClause.Count; i++)
                    {
                        if (i > 0) buffer.Append(", ");
                        if (query.OrderByClause[i].Direction == Thn.Application.Sort.SortDirection.Ascending)
                        {
                            buffer.Append(query.OrderByClause[i].FieldName);
                        }
                        else
                        {
                            buffer.Append(query.OrderByClause[i].FieldName + " DESC ");
                        }
                    }
                }

                //)
                buffer.AppendLine(")");

                #endregion

                #region Main query

                //SELECT
                buffer.Append("SELECT ");

                //        //"[Column], [Column], ..."
                BuildColumnNames(attributes, buffer, ""); buffer.AppendLine();

                //FROM SQLPaging
                buffer.AppendLine("FROM SQLPaging");

                //WHERE ResultNum > [skip]
                buffer.AppendLine(string.Format("WHERE ResultNum > {0}", query.ItemsToSkip));

                #endregion

                #endregion
            }
            else
            {
                #region Paging is not required -> let parent handles

                base.BuildSelectOneEntityClause(entity, attributes, criteria, buffer, valueStack, query);

                #endregion
            }

#if DEBUG
            string text = buffer.ToString();
            int tmp = 0;
#endif
        }
        #endregion

        #region Build Select One Entity clause - DOUBLE ROWCOUNT
        //*
        // * 
        // * HaiNM - 2012 Jul 6 - This technique supports SQL 2000 and later. But on SQL Server 2008 R2, when the primary identifier is GUID,
        // * there is a bug that always returns one row where there should be no more results. This technique is also supposed to be faster than
        // * the ROW_NUMBER method above.
        // * 
        // */

        ///// <summary>
        ///// Appends a Select record sql command to buffer
        ///// </summary>
        ///// <param name="entity">Object mapping</param>
        ///// <param name="attributes">fields to be selected</param>
        ///// <param name="criteria">condition model</param>
        ///// <param name="buffer">text buffer</param>
        ///// <param name="valueStack">list to append values to while parsing condition</param>
        ///// <param name="query">Optional query object</param>
        //protected override void BuildSelectOneEntityClause(ObjectMap entity, AttributeMap[] attributes, Condition criteria, StringBuilder buffer, Thn.Collections.ArrayList valueStack, SelectQuery query)
        //{
        //    if (query.ItemsToSkip >= 0 && query.ItemsToTake >= 0)
        //    {
        //        #region Paging is required -> special query

        //        #region TECHNIQUE EXPLANATION
        //        /*
        //         * This technique is based on using two SET ROWCOUNT calls (stop processing after rowcount number of rows). To read more about this technique, read
        //         * here: http://sqlserverplanet.com/sql-server/best-sql-server-pagination-method
        //         * 
        //         * Example sql query:

        //            USE [TEST];
        //            DECLARE @StartID UniqueIdentifier
        //            SET ROWCOUNT 10
        //            SELECT @StartID = ID
        //            FROM [USER]

        //            SET ROWCOUNT 5
        //            SELECT *
        //            FROM [User]
        //            WHERE ID >= @StartID                    

        //            SET ROWCOUNT 0

        //         */
        //        #endregion

        //        //AttributeMap map = attributes[0];//assume the first attribute is always the identifier
        //        AttributeMap map = FindIdentifier(attributes);

        //        #region First Select
        //        /*
        //         * First pass, find the first row after skip, save that row's ID as StartID
        //         */

        //        //DECLARE @RowStartID [type]
        //        if (map.DataAttributeTypeSpecified) buffer.AppendFormat("DECLARE @StartID {0} \r\n", map.DataAttributeType);
        //        else buffer.AppendFormat("DECLARE @StartID {0} \r\n", mDialect.GetDataAttributeAsString(map));


        //        //SET ROWCOUNT [skip]
        //        buffer.AppendFormat("SET ROWCOUNT {0} \r\n", query.ItemsToSkip + 1);

        //        //SELECT @StartID = [identifier]
        //        buffer.AppendFormat("SELECT @StartID = {0} \r\n", map.DataAttribute);

        //        //FROM [table]
        //        buffer.AppendFormat("FROM [{0}] \r\n", entity.DataEntity);

        //        //add where clause if available
        //        //" WHERE [column] = @conxxx, [column] = @conxxx ..."
        //        if (criteria != null)
        //        {
        //            buffer.AppendLine("WHERE " + ParseCondition(criteria, valueStack));
        //        }
        //        #region Defensive tracing
        //        else
        //        {
        //            //not required
        //        }
        //        #endregion

        //        #endregion

        //        #region Second Select
        //        /*
        //         * Second pass, find the rows to take, starting from the previous row
        //         */

        //        //SET ROWCOUNT [take]
        //        buffer.AppendFormat("SET ROWCOUNT {0} \r\n", query.ItemsToTake);

        //        //SELECT clause                
        //        buffer.Append("SELECT");

        //        //"[Column], [Column], ..."
        //        BuildColumnNames(attributes, buffer, "");

        //        //FROM [table]
        //        buffer.AppendFormat("\r\nFROM [{0}] \r\n", entity.DataEntity);

        //        //where clause
        //        if (criteria == null)
        //        {
        //            #region No criteria are available

        //            //WHERE [identifier] >= @StartID
        //            buffer.AppendFormat("WHERE {0} >= @StartID \r\n", map.DataAttribute);

        //            #endregion
        //        }
        //        else
        //        {
        //            #region Has criteria

        //            //WHERE [identifier] >= @StartID
        //            buffer.AppendFormat("WHERE {0} >= @StartID", map.DataAttribute);

        //            //" AND [column] = @conxxx, [column] = @conxxx ..."
        //            buffer.Append(" AND " + ParseCondition(criteria, valueStack) + "\r\n");

        //            #endregion
        //        }

        //        //SET ROWCOUNT 0
        //        buffer.AppendFormat("SET ROWCOUNT 0 \r\n");

        //        //string text = buffer.ToString();

        //        #endregion

        //        #endregion
        //    }
        //    else
        //    {
        //        #region Paging is not required -> let parent handles

        //        base.BuildSelectOneEntityClause(entity, attributes, criteria, buffer, valueStack, query);

        //        #endregion
        //    }

        //    /*
        //    //"SELECT "
        //    buffer.Append(sql.SelectRecord);

        //    //" DISTINCT "
        //    if ((query != null) && (query.HasDistinct == true)) buffer.Append(sql.Distinct);

        //    //"[Column], [Column], ..."
        //    BuildColumnNames(attributes, buffer, "");

        //    //" FROM [entity]"
        //    buffer.Append(sql.FromTable + sql.ItemStart + entity.DataEntity + sql.ItemEnd);

        //    //" WHERE [column] = @conxxx, [column] = @conxxx ..."
        //    if (criteria != null) BuildCondition(criteria, buffer, valueStack);

        //    //" ORDER BY "
        //    if ((query != null) && (query.OrderByClause != null) && (query.OrderByClause.Count > 0))
        //    {
        //        buffer.Append(sql.OrderBy);

        //        for (int i = 0; i < query.OrderByClause.Count; i++)
        //        {
        //            if (i > 0) buffer.Append(sql.ItemSeparator);
        //            if (query.OrderByClause[i].Direction == Thn.Application.Sort.SortDirection.Ascending)
        //            {
        //                buffer.Append(query.OrderByClause[i].FieldName);
        //            }
        //            else
        //            {
        //                buffer.Append(query.OrderByClause[i].FieldName + sql.Descending);
        //            }
        //        }
        //    }
        //    */
        //}        
        #endregion

        #region Constructors
        /// <summary>
        /// Default constructor
        /// </summary>
        public MsSqlDatabase()
        {
            this.Dialect = new MsSqlDialect();

            //Since Microsoft SQL Server uses connection pooling,
            //there is no need to keep connection open
            AlwaysCloseConnection = true;
        }
        #endregion
    }
}
